SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [Weekly].[GetPropBreakout2]
(
    @FundId AS VARCHAR (10),
    @NAVDate AS DATE
) AS
BEGIN
DECLARE @cols NVARCHAR(MAX)
DECLARE @ExportId INT = (SELECT MAX(e.ExportId) FROM FSFundModel.Reporting.Export e WHERE e.FundId =@FundId AND e.NAVDate = @NAVDate)
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT LTRIM(RTRIM(p.PortfolioId)) AS y FROM FSFundModel.dbo.Portfolio p WHERE p.FundId =@FundId) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');



SELECT * FROM (
		SELECT LTRIM(RTRIM(a.PortfolioId)) AS PortfolioId , ISNULL(ah.NBSReason, 'Broadly Syndicated / Other') AS PropReason, a.MktVal FROM FSFundModel.Reporting.Asset a INNER JOIN 
		ReportingScripts.Live.AssetHelper(@ExportId) ah
		ON a.AssetIntrnlId = ah.AssetIntrnlId

		WHERE a.ExportId = @ExportId
		AND a.IsActiveEndOfDay = 1) AS D
PIVOT (SUM(d.MktVal) FOR PortfolioId IN ([COBBS],[COOPR],[DELRI],[FSIC2],[LEHIG])) AS P;
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Weekly', 'SCHEMA', N'Weekly', 'PROCEDURE', N'GetPropBreakout2', NULL, NULL
GO
